<?php
class statisticsModel extends model
{
    public function getbigcatecory(){
        $category = $this->dao->select('*')->from(TABLE_BIGCATEGORY)->orderBy('id asc')->fetchAll();
        return $category;
    }

    public function getbigid(){

        $bigid = $this->dao->select('id')->from(TABLE_BIGCATEGORY)->orderBy('id asc')->fetchPairs();
        return $bigid;
    }

    public function getdata1($date){
        $date = date('Y',$date);
        $startDate    = "{$date}-00-01";
        $category = $this->getbigcatecory();
        $date =array();
        $startDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){

            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();

            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

            if($liandai->sum!=0){
                $val1->sum = $sum->sum+$liandai->sum;
            }else{
                $val1->sum = $sum->sum;
            }

            $date[] = $val1;
        }
        return $date;
    }

    public function getdata2($date){
        $date = date('Y',$date);
        $startDate    = "{$date}-00-01";
        $category = $this->getbigcatecory();
        $date =array();
        $startDate      = date('Y-m-d', strtotime("$startDate +2 month"));
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){
            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();

            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

            if($liandai->sum!=0){
                $val1->sum = $sum->sum+$liandai->sum;
            }else{
                $val1->sum = $sum->sum;
            }
            $date[] = $val1;
        }
        return $date;
    }

    public function getdata3($date){
        $date = date('Y',$date);
        $startDate    = "{$date}-00-01";
        $category = $this->getbigcatecory();
        $date =array();
        $startDate      = date('Y-m-d', strtotime("$startDate +3 month"));
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){
            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();
            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();


            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

            if($liandai->sum!=0){
                $val1->sum = $sum->sum+$liandai->sum;
            }else{
                $val1->sum = $sum->sum;
            }
            $date[] = $val1;
        }
        return $date;
    }

    public function getdata4($date){
        $date = date('Y',$date);
        $startDate    = "{$date}-00-01";
        $category = $this->getbigcatecory();
        $date =array();
        $startDate      = date('Y-m-d', strtotime("$startDate +4 month"));
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){
            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();
            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

            if($liandai->sum!=0){
                $val1->sum = $sum->sum+$liandai->sum;
            }else{
                $val1->sum = $sum->sum;
            }
            $date[] = $val1;
        }
        return $date;
    }

    public function getdata5($date){
        $date = date('Y',$date);
        $startDate    = "{$date}-00-01";
        $category = $this->getbigcatecory();
        $date =array();
        $startDate      = date('Y-m-d', strtotime("$startDate +5 month"));
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){
            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();
            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

            if($liandai->sum!=0){
                $val1->sum = $sum->sum+$liandai->sum;
            }else{
                $val1->sum = $sum->sum;
            }
            $date[] = $val1;
        }
        return $date;
    }

    public function getdata6($date){
        $date = date('Y',$date);
        $startDate    = "{$date}-00-01";
        $category = $this->getbigcatecory();
        $date =array();
        $startDate      = date('Y-m-d', strtotime("$startDate +6 month"));
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){
            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();
            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

            if($liandai->sum!=0){
                $val1->sum = $sum->sum+$liandai->sum;
            }else{
                $val1->sum = $sum->sum;
            }
            $date[] = $val1;
        }
        return $date;
    }
    public function getdata7($date){
        $date = date('Y',$date);
        $startDate    = "{$date}-00-01";
        $category = $this->getbigcatecory();
        $date =array();
        $startDate      = date('Y-m-d', strtotime("$startDate +7 month"));
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){
            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();
            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

            if($liandai->sum!=0){
                $val1->sum = $sum->sum+$liandai->sum;
            }else{
                $val1->sum = $sum->sum;
            }
            $date[] = $val1;
        }
        return $date;
    }
    public function getdata8($date){
        $date = date('Y',$date);
        $startDate    = "{$date}-00-01";
        $category = $this->getbigcatecory();
        $date =array();
        $startDate      = date('Y-m-d', strtotime("$startDate +8 month"));
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){
            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();
            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

            if($liandai->sum!=0){
                $val1->sum = $sum->sum+$liandai->sum;
            }else{
                $val1->sum = $sum->sum;
            }
            $date[] = $val1;
        }
        return $date;
    }

    public function getdata9($date){
        $date = date('Y',$date);
        $startDate    = "{$date}-00-01";
        $category = $this->getbigcatecory();
        $date9 =array();
        $startDate      = date('Y-m-d', strtotime("$startDate +9 month"));
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){
            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();
            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

            if($liandai->sum!=0){
                $val1->sum = $sum->sum+$liandai->sum;
            }else{
                $val1->sum = $sum->sum;
            }
            $date9[] = $val1;
        }
        return $date9;
    }

    public function getdata10($date){
        $date = date('Y',$date);
        $startDate    = "{$date}-00-01";
        $category = $this->getbigcatecory();
        $date =array();
        $startDate      = date('Y-m-d', strtotime("$startDate +10 month"));
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){
            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();

            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();



            if($liandai->sum!=0){
                $val1->sum = $sum->sum+$liandai->sum;
            }else{
                $val1->sum = $sum->sum;
            }
            $date[] = $val1;
        }
        return $date;
    }

    public function getdata11($date){
        $date = date('Y',$date);
        $startDate    = "{$date}-00-01";
        $category = $this->getbigcatecory();
        $date =array();
        $startDate      = date('Y-m-d', strtotime("$startDate +11 month"));
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){
            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();
            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

            if($liandai->sum!=0){
                $val1->sum = $sum->sum+$liandai->sum;
            }else{
                $val1->sum = $sum->sum;
            }
            $date[] = $val1;
        }
        return $date;
    }
    public function getdata12($date){
        $date = date('Y',$date);
        $startDate    = "{$date}-00-01";
        $category = $this->getbigcatecory();
        $date =array();
        $startDate      = date('Y-m-d', strtotime("$startDate +12 month"));
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){
            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();
            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

            if($liandai->sum!=0){
                $val1->sum = $sum->sum+$liandai->sum;
            }else{
                $val1->sum = $sum->sum;
            }
            $date[] = $val1;
        }
        return $date;
    }


    public function getcategorysum($date){

        $startDate    = $date;
        $category = $this->getbigcatecory();
        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));
        foreach($category as $key=>$val1){

            $sum1 = $this->dao->select('Sum(categoryscore)')->alias('sum')
                ->from(TABLE_STATISTICS)
                ->where('approval_status')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($val1->id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $val1->sum = $sum1->sum;
            $val1->startDate = $startDate;
            $val1->endDate = $endDate;

        }

        return $category;
    }


    public function getunit($startDate){
        //$date = date('Y-m',$date);
       // $startDate    = "{$date}-01";
        $category = $this->getbigid();

        $endDate      = date('Y-m-d', strtotime("$startDate +1 month"));

        $units = $this->dao->select('*')->from(TABLE_UNIT)->where('parent_id')->eq('000001')->orderBy('id asc')->fetchAlll();

        foreach($units as $uvl){

            $sum1 = $this->dao->select('Sum(categoryscore)')->alias('sum')
                ->from(TABLE_STATISTICS)
                ->where('approval_status')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('department_id')->eq($uvl->unit_id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $sum2 = $this->dao->select('Sum(categoryscore)')->alias('sum')
                ->from(TABLE_STATISTICS)
                ->where('approval_status')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('parent_id')->eq($uvl->unit_id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            $sums = $sum1->sum+$sum2->sum;
            $uvl->scale = $sums;
            $uvl->count = count($category);
            for($i=1;$i<=count($category);$i++){
                $csum1 = $this->dao->select('Sum(categoryscore)')->alias('sum')
                    ->from(TABLE_STATISTICS)
                    ->where('approval_status')->eq('final')
                    ->andWhere('istrue')->eq('yes')
                    ->andWhere('department_id')->eq($uvl->unit_id)
                    ->andWhere('bigcategory')->eq($category[$i])
                    ->andWhere('revocation')->ne('yes')
                    ->andWhere('`eventtime`')->ge($startDate)
                    ->andWhere('`eventtime`')->le($endDate)
                    ->fetch();

                $csum2 = $this->dao->select('Sum(categoryscore)')->alias('sum')
                    ->from(TABLE_STATISTICS)
                    ->where('approval_status')->eq('final')
                    ->andWhere('istrue')->eq('yes')
                    ->andWhere('parent_id')->eq($uvl->unit_id)
                    ->andWhere('bigcategory')->eq($category[$i])
                    ->andWhere('revocation')->ne('yes')
                    ->andWhere('`eventtime`')->ge($startDate)
                    ->andWhere('`eventtime`')->le($endDate)
                    ->fetch();
                $bid = 'bid'.$i;
                $uvl->$i = $csum1->sum+$csum2->sum;
                $uvl->$bid = $category[$i];
            }

            $uvl->startDate = $startDate;
            $uvl->endDate = $endDate;

        }

        return $units;
    }

    public function pstatis($unit="",$startDate){

        $unitID = $this->dao->select('id,unit_id')->from(TABLE_UNIT)->where('parent_id')->eq($unit)->orderBy('id asc')->fetchPairs();
        $unitID[$unit] = $unit;
        $unitID = implode($unitID,',');
        $endDate      = date('Y-m-d', strtotime("$startDate +12 month"));
        $category = $this->getbigid();
        $user = $this->dao->select('name,id_card')->from(TABLE_USERS)->where('unit_id')->in($unitID)->orderBy('id asc')->fetchAll();
        foreach($user as $value){

                $sum1 = $this->dao->select('Sum(categoryscore)')->alias('sum')
                    ->from(TABLE_STATISTICS)
                    ->where('approval_status')->eq('final')
                    ->andWhere('istrue')->eq('yes')
                    ->andWhere('personnel')->eq($value->id_card)
                    ->andWhere('revocation')->ne('yes')
                    ->andWhere('`eventtime`')->ge($startDate)
                    ->andWhere('`eventtime`')->le($endDate)
                    ->fetch();

                $sums = $sum1->sum;
                 $value->scale = $sums;
                $value->count = count($category);
                for($i=1;$i<=count($category);$i++){
                    $csum1 = $this->dao->select('Sum(categoryscore)')->alias('sum')
                        ->from(TABLE_STATISTICS)
                        ->where('approval_status')->eq('final')
                        ->andWhere('istrue')->eq('yes')
                        ->andWhere('personnel')->eq($value->id_card)
                        ->andWhere('bigcategory')->eq($category[$i])
                        ->andWhere('revocation')->ne('yes')
                        ->andWhere('`eventtime`')->ge($startDate)
                        ->andWhere('`eventtime`')->le($endDate)
                        ->fetch();

                    $bid = 'bid'.$i;
                    $value->$i = $csum1->sum;
                    $value->$bid = $category[$i];
                }

             $value->startDate = $startDate;
                $value->endDate = $endDate;

            }

        return $user;

        }

    public function getusername($idcard){

        $name = $this->dao->select('name')
            ->from(TABLE_USERS)
            ->where('id_card')->eq($idcard)
            ->fetch();
        return $name->name;
    }

    public function getusersum($idcard,$startDate,$bid){

        $endDate      = date('Y-m-d', strtotime("$startDate +12 month"));
        $csum1 = $this->dao->select('Sum(categoryscore)')->alias('sum')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('personnel')->eq($idcard)
            ->andWhere('bigcategory')->eq($bid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($startDate)
            ->andWhere('`eventtime`')->le($endDate)
            ->fetch();
        return $csum1->sum;

    }


    public function ptype($idcard,$startDate,$bid){

        $endDate      = date('Y-m-d', strtotime("$startDate +12 month"));
        $data = $this->dao->select('*')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('bigcategory')->eq($bid)
            ->andWhere('personnel')->eq($idcard)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($startDate)
            ->andWhere('`eventtime`')->le($endDate)
            ->orderBy('eventtime asc')
            ->fetchAll();
        foreach($data as $val){

            $name = $this->dao->select('name')
                ->from(TABLE_USERS)
                ->where('id_card')->eq($val->personnel)
                ->fetch();
            $val->name = $name->name;
        }

        return $data;
    }

    public function qxingqing($idcard,$startDate){

        $endDate      = date('Y-m-d', strtotime("$startDate +12 month"));
        $data = $this->dao->select('*')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('personnel')->eq($idcard)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($startDate)
            ->andWhere('`eventtime`')->le($endDate)
            ->orderBy('eventtime asc')
            ->fetchAll();
        foreach($data as $val){

            $name = $this->dao->select('name')
                ->from(TABLE_USERS)
                ->where('id_card')->eq($val->personnel)
                ->fetch();
            $val->name = $name->name;
        }

        return $data;

    }

    public function getuserallsum($idcard,$startDate){

        $endDate      = date('Y-m-d', strtotime("$startDate +12 month"));
        $csum1 = $this->dao->select('Sum(categoryscore)')->alias('sum')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('personnel')->eq($idcard)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($startDate)
            ->andWhere('`eventtime`')->le($endDate)
            ->fetch();
        return $csum1->sum;

    }


    public function getappraisal($date){
        $startDate    = "{$date}-01-01";

        $endDate      = date('Y-m-d', strtotime("$startDate +12 month"));
        $category = $this->lang->statistics->typeList;
        $category = array_flip($category);
        $category = array_values($category);

        $units = $this->dao->select('*')->from(TABLE_UNIT)->where('parent_id')->eq('000001')->orderBy('id asc')->fetchAlll();

        foreach($units as $uvl){

            for($i=0;$i<count($category);$i++){

                $unitID = $this->dao->select('id,unit_id')->from(TABLE_UNIT)->where('parent_id')->eq($uvl->unit_id)->orderBy('id asc')->fetchPairs();
                $unitID[$uvl->id] = $uvl->unit_id ;
                $unitID = implode($unitID,',');
                //$uvl->uid = $unitID;
                $sum2 = $this->dao->select('count(*)')->alias('count')
                    ->from(TABLE_PROCESS)
                    ->where('approval_status')->eq('final')
                    ->andWhere('istrue')->eq('yes')
                    ->andWhere('department_id')->in($unitID)
                    ->andWhere('scoretype')->eq($category[$i])
                    ->andWhere('revocation')->ne('yes')
                    ->andWhere('`eventtime`')->ge($startDate)
                    ->andWhere('`eventtime`')->le($endDate)
                    ->fetch();

                $sum3 = $this->dao->select('count(*)')->alias('count')
                    ->from(TABLE_LIANDAI)
                    ->where('istrue')->eq('yes')
                    ->andWhere('unit_id')->in($unitID)
                    ->andWhere('scoretype')->eq($category[$i])
                    ->andWhere('revocation')->ne('yes')
                    ->andWhere('`eventtime`')->ge($startDate)
                    ->andWhere('`eventtime`')->le($endDate)
                    ->fetch();

                $s = $category[$i];
                //return $category[$i];
                $uvl->$s = $sum2->count+$sum3->count;
               // return $uvl->$s;
            }

            $sums =100-$uvl->score*0.5-$uvl->notification*0.5-$uvl->zlth*1-$uvl->remind*2-$uvl->admonishing*3;

           $uvl->sums = $sums;

          $uvl->startDate = $startDate;
            $uvl->endDate = $endDate;

        }

        return $units;
    }

    public function pappraisal($uid,$type,$start,$end){

        $unitID = $this->dao->select('id,unit_id')->from(TABLE_UNIT)->where('parent_id')->eq($uid)->orderBy('id asc')->fetchPairs();
        $unitID[$uid] = $uid;
        $unitID = implode($unitID,',');

        $sum2 = $this->dao->select('*')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('department_id')->in($unitID)
            ->andWhere('scoretype')->eq($type)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($start)
            ->andWhere('`eventtime`')->le($end)
            ->fetchAll();

        foreach($sum2 as $val){

            $name = $this->dao->select('name')
                ->from(TABLE_USERS)
                ->where('id_card')->eq($val->personnel)
                ->fetch();
            $val->name = $name->name;
        }

        return $sum2;
    }


    public function getunitsum($unitid,$start,$end){

        $sum1 = $this->dao->select('Sum(categoryscore)')->alias('sum')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('department_id')->eq($unitid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($start)
            ->andWhere('`eventtime`')->le($end)
            ->fetch();

        $sum2 = $this->dao->select('Sum(categoryscore)')->alias('sum')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('parent_id')->eq($unitid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($start)
            ->andWhere('`eventtime`')->le($end)
            ->fetch();

        $sums = $sum1->sum+$sum2->sum;

        return $sums;
    }

    public function getunitcategorysum($unitid,$bid,$start,$end){

        $sum1 = $this->dao->select('Sum(categoryscore)')->alias('sum')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('department_id')->eq($unitid)
            ->andWhere('bigcategory')->eq($bid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($start)
            ->andWhere('`eventtime`')->le($end)
            ->fetch();

        $sum2 = $this->dao->select('Sum(categoryscore)')->alias('sum')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('parent_id')->eq($unitid)
            ->andWhere('bigcategory')->eq($bid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($start)
            ->andWhere('`eventtime`')->le($end)
            ->fetch();

        $sums = $sum1->sum+$sum2->sum;

        return $sums;
    }

    public function getsumt($pid,$start,$end){

        $sum1 = $this->dao->select('Sum(categoryscore)')->alias('sum')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('bigcategory')->eq($pid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($start)
            ->andWhere('`eventtime`')->le($end)
            ->fetch();

        $sums = $sum1->sum;

        return $sums;
    }

    public function scategoryt($pid,$start,$end){

        $sum2 = $this->dao->select('*')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('bigcategory')->eq($pid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($start)
            ->andWhere('`eventtime`')->le($end)
            ->orderBy('eventtime asc')
            ->fetchAll();
        $data = $sum2;
        foreach($data as $val){

            $name = $this->dao->select('name')
                ->from(TABLE_USERS)
                ->where('id_card')->eq($val->personnel)
                ->fetch();
            $val->name = $name->name;
        }

        return $data;


    }

    public function punits($unitid,$start,$end){

        $sum2 = $this->dao->select('*')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('parent_id')->eq($unitid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($start)
            ->andWhere('`eventtime`')->le($end)
            ->orderBy('eventtime asc')
            ->fetchAll();

        $sum1 = $this->dao->select('*')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('department_id')->eq($unitid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($start)
            ->andWhere('`eventtime`')->le($end)
            ->orderBy('eventtime asc')
            ->fetchAll();

        $data = $sum2+$sum1;

        foreach($data as $val){

            $name = $this->dao->select('name')
                ->from(TABLE_USERS)
                ->where('id_card')->eq($val->personnel)
                ->fetch();
            $val->name = $name->name;
        }

        return $data;


    }

    public function cunits($unitid,$bid,$start,$end){

        $sum2 = $this->dao->select('*')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('parent_id')->eq($unitid)
            ->andWhere('bigcategory')->eq($bid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($start)
            ->andWhere('`eventtime`')->le($end)
            ->orderBy('eventtime asc')
            ->fetchAll();

        $sum1 = $this->dao->select('*')
            ->from(TABLE_STATISTICS)
            ->where('approval_status')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('department_id')->eq($unitid)
            ->andWhere('bigcategory')->eq($bid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($start)
            ->andWhere('`eventtime`')->le($end)
            ->orderBy('eventtime asc')
            ->fetchAll();

        $data = $sum2+$sum1;

        foreach($data as $val){

            $name = $this->dao->select('name')
                ->from(TABLE_USERS)
                ->where('id_card')->eq($val->personnel)
                ->fetch();
            $val->name = $name->name;
        }

        return $data;

    }

    public function getunitname($id){

        $unit = $this->dao->select('unit_name')->from(TABLE_UNIT)->where('unit_id')->eq($id)->fetch();
        return $unit->unit_name;
    }

    public function scategory($pid,$start,$end){

        $proid = $this->dao->select('processid')
            ->from(TABLE_DEDUCTMARKS)
            ->where('state')->eq('final')
            ->andWhere('istrue')->eq('yes')
            ->andWhere('bigid')->eq($pid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`createting`')->ge($start)
            ->andWhere('`createting`')->le($end)
            ->fetchPairs();

        $ldid = $this->dao->select('orders')
            ->from(TABLE_LIANDAI)
            ->where('istrue')->eq('yes')
            ->andWhere('bigcategory')->eq($pid)
            ->andWhere('revocation')->ne('yes')
            ->andWhere('`eventtime`')->ge($start)
            ->andWhere('`eventtime`')->le($end)
            ->fetchPairs();

        $plid = array_merge($proid,$ldid);
        $plid = array_unique($plid);
        $plid = implode(',',$plid);

        $scategory = $this->dao->select('*')
                        ->from(TABLE_PROCESS)
                        ->where('orders')->in($plid)
                        ->andWhere('revocation')->ne('yes')
                        ->fetchAll();

        foreach($scategory as $val){

            $name = $this->dao->select('name')
                    ->from(TABLE_USERS)
                     ->where('id_card')->eq($val->personnel)
                    ->fetch();
            $val->name = $name->name;

            $category = $this->dao->select('categoryname')
                        ->from(TABLE_DEDUCTMARKS)
                        ->where('processid')->eq($val->orders)
                        ->andWhere('revocation')->ne('yes')
                        ->andWhere('bigid')->eq($pid)
                        ->fetchPairs();
            $category = implode(',',$category);
            $val->category = $category;

            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('processid')->eq($val->orders)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('bigid')->eq($pid)
                ->fetch();
            $val->sum = $sum->sum;

            $liandai = $this->dao->select('*')
                        ->from(TABLE_LIANDAI)
                        ->where('orders')->eq($val->orders)
                        ->andWhere('bigcategory')->eq($pid)
                        ->andWhere('revocation')->ne('yes')
                        ->fetchAll();
            foreach($liandai as $lval){
                $lname = $this->dao->select('name')
                        ->from(TABLE_USERS)
                        ->where('id_card')->eq($lval->user)
                        ->fetch();
                $lval->user = $lname->name;
            }
           $val->liandai = $liandai;

        }

        return $scategory;

    }

    public function getsum($id,$startDate,$endDate){

            $sum = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_DEDUCTMARKS)
                ->where('state')->eq('final')
                ->andWhere('istrue')->eq('yes')
                ->andWhere('bigid')->eq($id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`createting`')->ge($startDate)
                ->andWhere('`createting`')->le($endDate)
                ->fetch();
            $liandai = $this->dao->select('Sum(score)')->alias('sum')
                ->from(TABLE_LIANDAI)
                ->where('istrue')->eq('yes')
                ->andWhere('bigcategory')->eq($id)
                ->andWhere('revocation')->ne('yes')
                ->andWhere('`eventtime`')->ge($startDate)
                ->andWhere('`eventtime`')->le($endDate)
                ->fetch();

            if($liandai->sum!=0){
                $sum = $sum->sum+$liandai->sum;
            }else{
                $sum = $sum->sum;
            }

        return $sum;
    }

        public function getcnamebyid($id){

            $categoryname = $this->dao->select('mistake')->from(TABLE_BIGCATEGORY)->where('id')->eq($id)->fetch();
            return $categoryname->mistake;
        }

    public function getdeductmarks(){

        $deductmarks = $this->dao->select('*')->from(TABLE_DEDUCTMARKS)
                        ->where('state')->eq('final')
                        ->fetchAll();
        foreach($deductmarks as $val){

            $process = $this->dao->select('*')->from(TABLE_PROCESS)->where('orders')->eq($val->processid)->fetch();
            $unit = $this->dao->select('*')->from(TABLE_UNIT)->where('unit_id')->eq($process->department_id)->fetch();
            $val->unit_id = $unit->unit_id;
            $val->unit_name = $unit->unit_name;
            $val->sort = $unit->sort;
            $val->parent_id = $unit->parent_id;
            $val->monitors_idcard = $process->monitors_idcard;
            $val->monitors_department_id = $process->monitors_department_id;
            $val->monitors_department_name = $process->monitors_department_name;
            $val->m_inputtime = $process->m_inputtime;
            $val->eventtime = $process->eventtime;
            $val->address = $process->address;
            $val->department_heads = $process->department_heads;
            $val->personnel = $process->personnel;
            $val->persontime = $process->persontime;
            $val->confirmedtime = $process->confirmedtime;
            $val->supervision_approver = $process->supervision_approver;
            $val->approval_time = $process->approval_time;
            $val->approval_status = $process->approval_status;
            $val->revocation = $process->revocation;
        }

        return $deductmarks;

    }

    public function insertstatic(){

        $deductmarks = $this->getdeductmarks();

        foreach($deductmarks as $val){

            $this->dao->insert(TABLE_STATISTICS)
                ->set('monitors_idcard')->eq($val->monitors_idcard)
                ->set('monitors_department_id')->eq($val->monitors_department_id)
                ->set('monitors_department_name')->eq($val->monitors_department_name)
                ->set('m_inputtime')->eq($val->m_inputtime)
                ->set('eventtime')->eq($val->eventtime)
                ->set('address')->eq($val->address)
                ->set('department_id')->eq($val->unit_id)
                ->set('department_name')->eq($val->unit_name)
                ->set('sort')->eq($val->sort)
                ->set('department_heads')->eq($val->department_heads)
                ->set('personnel')->eq($val->personnel)
                ->set('persontime')->eq($val->persontime)
                ->set('bigcategory')->eq($val->bigid)
                ->set('bigcategoryname')->eq($val->bigname)
                ->set('category')->eq($val->categoryid)
                ->set('categoryname')->eq($val->categoryname)
                ->set('categoryscore')->eq($val->score)
                ->set('confirmedtime')->eq($val->confirmedtime)
                ->set('supervision_approver')->eq($val->supervision_approver)
                ->set('approval_time')->eq($val->approval_time)
                ->set('approval_status')->eq($val->approval_status)
                ->set('istrue')->eq($val->istrue)
                ->set('orders')->eq($val->processid)
                ->set('revocation')->eq($val->revocation)
                ->set('parent_id')->eq($val->parent_id)
                ->set('liandai')->eq('no')
                ->exec();
        }
        return !dao::isError();
    }

    public function getliandai(){

        $liandai = $this->dao->select('*')->from(TABLE_LIANDAI)->fetchAll();

        foreach($liandai as $val){

            $process = $this->dao->select('*')->from(TABLE_PROCESS)->where('orders')->eq($val->orders)->fetch();
            $val->monitors_idcard = $process->monitors_idcard;
            $val->monitors_department_id = $process->monitors_department_id;
            $val->monitors_department_name = $process->monitors_department_name;
            $val->m_inputtime = $process->m_inputtime;
            $val->eventtime = $process->eventtime;
            $val->address = $process->address;
            $val->approval_status = $process->approval_status;
            $val->approval_time = $process->approval_time;
            $val->supervision_approver = $process->supervision_approver;
            $unit = $this->dao->select('*')->from(TABLE_UNIT)->where('unit_id')->eq($val->unit_id)->fetch();
            $val->sort = $unit->sort;
            $val->parent_id = $unit->parent_id;

        }

        return $liandai;
    }

    public function insertlintstatic(){

        $liandai = $this->getliandai();
        foreach($liandai as $val){

            $this->dao->insert(TABLE_STATISTICS)
                ->set('monitors_idcard')->eq($val->monitors_idcard)
                ->set('monitors_department_id')->eq($val->monitors_department_id)
                ->set('monitors_department_name')->eq($val->monitors_department_name)
                ->set('m_inputtime')->eq($val->m_inputtime)
                ->set('eventtime')->eq($val->eventtime)
                ->set('address')->eq($val->address)
                ->set('department_id')->eq($val->unit_id)
                ->set('department_name')->eq($val->unit_name)
                ->set('sort')->eq($val->sort)
                ->set('department_heads')->eq($val->department_heads)
                ->set('personnel')->eq($val->user)
                ->set('bigcategory')->eq($val->bigcategory)
                ->set('bigcategoryname')->eq($val->bigcategoryname)
                ->set('category')->eq($val->category)
                ->set('categoryname')->eq($val->categoryname)
                ->set('categoryscore')->eq($val->score)
                ->set('supervision_approver')->eq($val->supervision_approver)
                ->set('approval_time')->eq($val->approval_time)
                ->set('approval_status')->eq($val->approval_status)
                ->set('istrue')->eq($val->istrue)
                ->set('orders')->eq($val->orders)
                ->set('revocation')->eq($val->revocation)
                ->set('parent_id')->eq($val->parent_id)
                ->set('liandai')->eq('yes')
                ->exec();
        }
        return !dao::isError();
    }

    public function setpscore(){

        $process = $this->dao->select('id,scoretype,scoretypename')->from(TABLE_PROCESS)->where('approval_status')->eq('final')->fetchAll();
        foreach($process as $val){

            if($val->scoretype==""&&$val->scoretypename==""){
                $this->dao->update(TABLE_PROCESS)
                    ->set('scoretype')->eq('score')
                    ->set('scoretypename')->eq('扣分')
                    ->where('id')->eq($val->id)
                    ->exec();
            }
        }

        return !dao::isError();

    }

    public function setdscore(){

        $process = $this->dao->select('id,scoretype,scoretypename')->from(TABLE_DEDUCTMARKS)->where('approval_status')->eq('final')->fetchAll();
        foreach($process as $val){

            if($val->scoretype==""&&$val->scoretypename==""){
                $this->dao->update(TABLE_DEDUCTMARKS)
                    ->set('scoretype')->eq('score')
                    ->set('scoretypename')->eq('扣分')
                    ->where('id')->eq($val->id)
                    ->exec();
            }
        }

        return !dao::isError();

    }

    public function setLscore(){

        $process = $this->dao->select('id,scoretype,scoretypename')->from(TABLE_LIANDAI)->fetchAll();
        foreach($process as $val){

            if($val->scoretype==""&&$val->scoretypename==""){
                $this->dao->update(TABLE_LIANDAI)
                    ->set('scoretype')->eq('score')
                    ->set('scoretypename')->eq('扣分')
                    ->where('id')->eq($val->id)
                    ->exec();
            }
        }

        return !dao::isError();

    }

    public function setSscore(){

        $process = $this->dao->select('id,scoretype,scoretypename')->from(TABLE_STATISTICS)->where('approval_status')->eq('final')->fetchAll();
        foreach($process as $val){

            if($val->scoretype==""&&$val->scoretypename==""){
                $this->dao->update(TABLE_STATISTICS)
                    ->set('scoretype')->eq('score')
                    ->set('scoretypename')->eq('扣分')
                    ->where('id')->eq($val->id)
                    ->exec();
            }
        }

        return !dao::isError();

    }

}